package worklog.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import worklog.dto.ProjectDTO;



public class ProjectDB {
	private MyDB db;

    public ProjectDB(){ 
    	try { db = new MyDB(); }
    	catch(Exception e){ db = null; }
    }
	protected void finalize(){ db.close(); }

	public void close(){ db.close(); }
    
	public Boolean isReady(){ return (this.db!=null); }

	
	public Boolean favoriteProject(int userId, int projectId){
		PreparedStatement stat = null;
		int res = -1;
		try {
			if (checkUserProject(userId, projectId)){
				stat = db.connection.prepareStatement("UPDATE user_project SET isFav = 1 WHERE userId = ? AND projectId = ?");
			}
			else { 
				stat = db.connection.prepareStatement("INSERT INTO user_project (userId, projectId, isFav) VALUES (?, ?, 1)");
			}
			
			stat.setInt(1, userId);
			stat.setInt(2, projectId);
			res = stat.executeUpdate();
			stat.close();
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}
		
		if (res!=-1) return true;
		else return false;
	}	
	
	public Boolean unFavoriteProject(int userId, int projectId){
		PreparedStatement stat = null;
		int res = -1;
		try {
			stat = db.connection.prepareStatement("UPDATE user_project SET isFav = 0 WHERE userId = ? AND projectId = ?");
			stat.setInt(1, userId);
			stat.setInt(2, projectId);
			res = stat.executeUpdate();
			stat.close();
		} 
		catch (SQLException e) {}
		
		if (res!=-1) return true;
		else return false;
	}	
	
	
	/**
	 * Add new project
	 * @return	new project ID
	 */
	public int addProject(ProjectDTO project){
		PreparedStatement statement = null;
		int id = -1;
		try {
			statement = db.connection.prepareStatement("INSERT INTO project (userID, name, archive) VALUES (0, ?, ?)", Statement.RETURN_GENERATED_KEYS);
			statement.setString(1, project.getName());
			statement.setBoolean(2, project.getArchive());
			statement.executeUpdate();
			ResultSet rskey = statement.getGeneratedKeys();
			if (rskey != null && rskey.next()) id = rskey.getInt(1);
			statement.close(); 
		} 
		catch (SQLException e){
			e.printStackTrace();
		}
		return id;
	}	
	
	/**
	 * Remove Project from the DB
	 */
	public int deleteProject(int projectId){
		PreparedStatement stat = null;
		int res = -1;
		try {
			stat = db.connection.prepareStatement("DELETE FROM project WHERE id = ?");
			stat.setInt(1, projectId);
			res = stat.executeUpdate();
			stat.close();
		} 
		catch (SQLException e){}
		return res;
	}	
	
	/**
	 * Update project details
	 */
	public int updateProject(ProjectDTO project, int userId){
		PreparedStatement stat = null;
		int res = -1;
		try {
			stat = db.connection.prepareStatement("UPDATE project SET name = ?, archive = ? WHERE id = ?");
			stat.setString(1, project.getName());
			stat.setBoolean(2, project.getArchive());
			stat.setInt(3, project.getId());
			res = stat.executeUpdate();
			stat.close();
			
			if (res != -1 && !project.getTags().isEmpty()) res = updateProjectTags(project, userId);
		} 
		catch (SQLException e){
			e.printStackTrace();
		}
		return res;
	}	

	
	public int updateProjectTags(ProjectDTO project, int userId){
		PreparedStatement stat = null;
		int res = -1;
		try {
			if (checkUserProject(userId, project.getId())){ 
				stat = db.connection.prepareStatement("UPDATE user_project SET tags = ? WHERE userId = ? AND projectId = ?");
			}
			else {
				stat = db.connection.prepareStatement("INSERT INTO user_project (tags, userId, projectId) VALUES (?, ?, ?)");
			}
			stat.setString(1, project.getTags());
			stat.setInt(2, userId);
			stat.setInt(3, project.getId());
			res = stat.executeUpdate();
			stat.close();
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}
		return res;
	}	
	
	
	/**
	 * Check if user_project already exist
	 */
	public Boolean checkUserProject(int userId, int projectId){
		PreparedStatement statement = null;
		ResultSet res = null;
		Boolean projectExists = false;
		try {
			statement = db.connection.prepareStatement("SELECT id FROM user_project WHERE userId = ? AND projectId = ?");
			statement.setInt(1, userId);
			statement.setInt(2, projectId);
			res = statement.executeQuery();			
			if (res != null && res.next()) projectExists = true;
			res.close();
			statement.close(); 
		} 
		catch (SQLException e){
			e.printStackTrace();
		}		
		return projectExists;
	}
	
	
	
	/**
	 * Check if project name already exist
	 */
	public Boolean checkProjectName(String name){
		PreparedStatement statement = null;
		ResultSet res = null;
		Boolean projectExists = false;
		try {
			statement = db.connection.prepareStatement("SELECT * FROM project WHERE name = ?");
			statement.setString(1, name);
			res = statement.executeQuery();			
			if (res != null && res.next()) projectExists = true;
			res.close();
			statement.close(); 
		} 
		catch (SQLException e){
			e.printStackTrace();
		}		
		return projectExists;
	}
	
	
	
	/**
	 * Retrieves 1 project by its ID
	 */
	public ProjectDTO getProject(int id, int userId){
		PreparedStatement statement = null;
		ProjectDTO project = new ProjectDTO();
		ResultSet res = null;
		try {
			/*
			statement = db.connection.prepareStatement("SELECT project.*, user_project.tags, user_project.isFav "+
				"FROM project LEFT JOIN user_project ON user_project.projectId = project.id AND user_project.userId = ? "+
				"WHERE project.id = ?");
			*/
			statement = db.connection.prepareStatement("SELECT project.id, project.name, project.archive, user_project.tags, user_project.isFav, COUNT(entry.id) AS entries "+
				"FROM entry LEFT JOIN project ON entry.projectID = project.id "+
				"LEFT JOIN user_project ON user_project.projectId = entry.projectID AND user_project.userId = ? "+ 
				"WHERE project.id = ? AND entry.projectID = project.id");
			
			statement.setInt(1, userId);
			statement.setInt(2, id);
			res = statement.executeQuery();		

			if (res != null && res.next()){
				project = new ProjectDTO();
				project.set(res);
			}
		} 
		catch (SQLException e){}
		
		return project;
	}

	
	/**
	 * Retrieves all projects
	 */
	public ArrayList<ProjectDTO> getProject() throws SQLException {
		return getProject(-1);
	}
	
	/**
	 * Retrieves all projects for a given user
	 */
	public ArrayList<ProjectDTO> getProject(int userId) throws SQLException {
		PreparedStatement statement = null;
		ResultSet res = null;
		ArrayList<ProjectDTO> projectList = new ArrayList<ProjectDTO>();
		
		if (userId == -1){		// all projects for admin
			//statement = db.connection.prepareStatement("SELECT * FROM project ORDER BY name");
			statement = db.connection.prepareStatement("SELECT project.id, project.name, project.archive, COUNT(entry.id) AS entries " +
					"FROM project LEFT JOIN entry ON project.id = entry.projectID " +
					"GROUP BY project.id ORDER BY project.name");
		}
		else {					// user projects for sidebar panel
			statement = db.connection.prepareStatement("SELECT project.*, user_project.tags, user_project.isFav "+
					"FROM project LEFT JOIN user_project ON user_project.projectId=project.id AND user_project.userId = ? WHERE project.archive = 0 ORDER BY name");
			statement.setInt(1, userId);
		}
		res = statement.executeQuery();		

		while (res.next()){
			ProjectDTO project = new ProjectDTO();
			project.set(res);
			projectList.add(project);
		}

		statement.close();
		
		if (projectList.size() > 0) return projectList;
		return null;
	}
    

}
